﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SQLite;
using Cashier_DAO.db;

namespace Cashier.mysql
{
    public class ImportMySQLData
    {
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        protected MySqlConnection getConnection()
        {
            MySqlConnection myConn = null;
            try
            {
                String connStr = String.Format("server={0};user id={1}; password={2}; database={3}; pooling=true",
                    "127.0.0.1", "root", "000000", "cashier");
                myConn = new MySqlConnection(connStr);
                myConn.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return myConn;
        }

        protected DataTable GetMySQLChinaarea()
        {
            DataTable dt = null;
            try
            {
                using (MySqlConnection mysqlConn = this.getConnection())
                {
                    String sql = "select * from dic_chinaarea order by id asc";
                    MySqlCommand cmd = new MySqlCommand(sql, mysqlConn);
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    dt = new DataTable("chinaarea");
                    da.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return dt;
        }

        /// <summary>
        /// 导入中国行政区信息数据
        /// </summary>
        public void ImportChinacityData()
        {
            int count = 0;
            try
            {
                DataTable dt = this.GetMySQLChinaarea();
                if (dt != null && dt.Rows.Count > 0)
                {
                    using (SQLiteConnection conn = (SQLiteConnection)ConnectionFactory.GetDbConnection())
                    {
                        conn.Open();
                        String sql = "insert into dic_chinacity(code,name,parent) values(@code,@name,@parent)";
                        SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                        foreach (DataRow lopRow in dt.Rows)
                        {
                            cmd.Parameters.Add("@code", DbType.String);
                            cmd.Parameters["@code"].Value = lopRow["code"].ToString();

                            cmd.Parameters.Add("@name", DbType.String);
                            cmd.Parameters["@name"].Value = lopRow["name"].ToString();

                            cmd.Parameters.Add("@parent", DbType.String);
                            cmd.Parameters["@parent"].Value = lopRow["father"].ToString();

                            count += cmd.ExecuteNonQuery();
                            Console.WriteLine(count + "-" + lopRow["name"].ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine(count);
        }

        /// <summary>
        /// 查询SQLite中的所有表
        /// </summary>
        /// <returns></returns>
        private DataTable GetSQLiteTables()
        {
            DataTable dt = null;
            try
            {
                using (SQLiteConnection conn = (SQLiteConnection)ConnectionFactory.GetDbConnection())
                {
                    conn.Open();
                    String sql = "select name from sqlite_master where type='table'";
                    SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    dt = new DataTable("sqlite_master");
                    da.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return dt;
        }

        /// <summary>
        /// 将SQLite表中的数据导入MySQL表中
        /// </summary>
        public void ImportSQLiteData()
        {
            try
            {
                DataTable dt = this.GetSQLiteTables();
                if (dt != null)
                {
                    using (SQLiteConnection conn = (SQLiteConnection)ConnectionFactory.GetDbConnection())
                    {
                        conn.Open();
                        String tablename = String.Empty;
                        String sql = String.Empty;
                        foreach (DataRow row in dt.Rows)
                        {
                            tablename = row[0].ToString();
                            //!tablename.Contains("sqlite")
                            if (tablename.Equals("member"))
                            {
                                sql = "select * from " + tablename + " order by id asc";
                                SQLiteCommand cmd = new SQLiteCommand(sql, conn);
                                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                                DataTable table = new DataTable(tablename);
                                da.Fill(table);

                                this.InsertMySQLTable(table);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        protected void InsertMySQLTable(DataTable dt)
        {
            try
            {
                using (MySqlConnection conn = this.getConnection())
                {
                    String tablename = dt.TableName;
                    foreach (DataRow row in dt.Rows)
                    {
                        StringBuilder sbField = new StringBuilder();
                        StringBuilder sbValue = new StringBuilder();
                        sbField.Append("insert into ").Append(tablename).Append("(");
                        sbValue.Append(") values(");
                        foreach (DataColumn column in dt.Columns)
                        {
                            sbField.Append(column.ColumnName).Append(",");
                            String typeName = column.DataType.Name;
                            object obj = row[column.ColumnName];
                            if (typeName.ToLower().Equals("string") || typeName.ToLower().Equals("char") || typeName.ToLower().Equals("datetime")
                                || typeName.ToLower().Equals("date") || typeName.ToLower().Equals("time"))
                            {
                                sbValue.Append("'").Append(obj).Append("',");
                            }
                            else
                            {
                                sbValue.Append(String.IsNullOrEmpty(obj.ToString()) ? 0 : obj).Append(",");
                            }
                        }
                        sbField.Remove(sbField.Length - 1, 1);
                        sbValue.Remove(sbValue.Length - 1, 1);
                        sbValue.Append(")");
                        sbField.Append(sbValue.ToString());
                        Console.WriteLine(sbField.ToString());


                        MySqlCommand cmd = new MySqlCommand(sbField.ToString(), conn);
                        Console.WriteLine(cmd.ExecuteNonQuery());

                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}
